using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Xml.Schema;

namespace ISYBAUSchemaExporter
{
    abstract public class OracleSchemaNode
    {
        private string mName;
        private XmlSchemaType mSchemaNode;

        public OracleSchemaNode(string name, XmlSchemaType schemaNode)
        {
            mName = name;
            mSchemaNode = schemaNode;
        }

        public string Name
        {
            get { return mName; }
        }

        public XmlSchemaType SchemaNode
        {
            get { return mSchemaNode; }
        }

        public bool IsOracleKeyword(string name)
        {
            return name.ToLower().Equals("start");
        }

        abstract public string SQLExpress();
        
        abstract public string OriginalSchemaName();

        abstract protected string FilteredDBName
        {
            get;
        }

        abstract public string DatabaseName
        {
            get;
        }
    }

	public class OracleSchemaDomainValue
    {
        private int mKey;
        private string mShortValue;
        private string mValue;

        public OracleSchemaDomainValue(int key, string value, string shortValue)
        {
            mKey = key;
            mValue = value;
            mShortValue = shortValue;
        }

        public int Key
        {
            get { return mKey; }
        }

        public string Value
        {
            get { return mValue; }
        }

        public string ShortValue
        {
            get { return mShortValue; }
        }
    }

	public class OracleSchemaField : OracleSchemaNode
    {
        private bool mNullable;

        private string mTypeExpress;

        public OracleSchemaField(string name, XmlSchemaType schemaNode, bool nullable)
            : base(name, schemaNode)
        {
            mNullable = nullable;
            mTypeExpress = string.Empty;
        }

        public override string SQLExpress()
        {
            return string.Format("  {0} {1} {2}", DatabaseName, GetTypeString(), mNullable ? "NULL" : "NOT NULL");
        }

        public override string DatabaseName
        {
            get {
                return (FilteredDBName.Length > 30 ? FilteredDBName.Substring(0, 30) : FilteredDBName);                
            }
        }

        protected override string FilteredDBName
        {
            get {
                string limitedName = Name.Replace('-', '_');
                limitedName = limitedName.Replace(".", string.Empty);
                if (IsOracleKeyword(limitedName))
                {
                    limitedName = limitedName + "_Col";
                }
                return limitedName;
            }
        }

        public string TypeExpress
        {
            get { return mTypeExpress; }
            set { mTypeExpress = value; }
        }
        
        public bool Nullable
        {
            get { return mNullable; }
        }

        public override string OriginalSchemaName()
        {
            if (SchemaNode != null && SchemaNode.Name != null && SchemaNode.Name != string.Empty)
            {
                return SchemaNode.Name;
            }
            return Name;
        }

        private string GetTypeString()
        {
            string result = string.Empty;
            if (string.IsNullOrEmpty(mTypeExpress) && SchemaNode != null)
            {
                switch (SchemaNode.TypeCode)
                {                    
                    case XmlTypeCode.Date:
                        result = "VARCHAR2(255)";
                        /// TODO: currently we just store the data type as VARCHAR
                        break;
                    case XmlTypeCode.Boolean:
                        result = "NUMBER(1,0)";
                        break;
                    case XmlTypeCode.Decimal:
                    case XmlTypeCode.Double:
                        result = "NUMBER(20,8)";
                        break;
                    case XmlTypeCode.Int:
                    case XmlTypeCode.Integer:
                        result = "NUMBER(10,0)";
                        break;
                    case XmlTypeCode.String:                       
                    default:
                        result = "VARCHAR2(255)";
                        break;
                }
            }
            else
            {
                result = mTypeExpress;
            }
            return result;
        }
    }    

    public class OracleSchemaTable : OracleSchemaNode
    {
        private List<OracleSchemaField> mFields;
        private bool mInitialized;

        public OracleSchemaTable(string name, XmlSchemaType schemaNode)
            : base(name, schemaNode)
        {
            mFields = new List<OracleSchemaField>();
            mInitialized = false;
        }

        public List<OracleSchemaField> Fields
        {
            get { return mFields; }
        }

        public override string SQLExpress()
        {
            string fieldsQuery = string.Empty;
            List<OracleSchemaTable> dependencyTableList = new List<OracleSchemaTable>();

            if (Fields.Count > 0)
            {
                if (Fields.Count > 1)
                {
                    for (int index = 0; index < Fields.Count - 1; index++)
                    {
                        fieldsQuery += string.Format("  {0},{1}", Fields[index].SQLExpress(), Environment.NewLine);
                        if (Fields[index] is OracleSchemaCollectionField)
                        {
                            dependencyTableList.Add((Fields[index] as OracleSchemaCollectionField).CollectionTable);
                        }
                    }
                }
                fieldsQuery += string.Format("  {0}{1}", Fields[Fields.Count - 1].SQLExpress(), Environment.NewLine);
                if (Fields[Fields.Count - 1] is OracleSchemaCollectionField)
                {
                    dependencyTableList.Add((Fields[Fields.Count - 1] as OracleSchemaCollectionField).CollectionTable);
                }
            }

            string depenTableSql = string.Empty;
            if (dependencyTableList.Count > 0)
            {
                foreach (OracleSchemaTable item in dependencyTableList)
                {
                    depenTableSql += item.SQLExpress();
                }
            }
            return string.Format("CREATE TABLE {0} ({1}{2});{3}{4}", DatabaseName, Environment.NewLine, fieldsQuery, Environment.NewLine, depenTableSql);
        }

        public string TypeName()
        {
            return SchemaNode.Name;
        }

        public override string DatabaseName
        {
            get {
                return (FilteredDBName.Length > 30 ? FilteredDBName.Substring(0, 30) : FilteredDBName);                
            }
        }

        protected override string FilteredDBName
        {
            get { return Name.Replace('-','_'); }
        }
        public void AddField(OracleSchemaField field)
        {
            mFields.Add(field);
        }

        public OracleSchemaField GetField(int index)
        {
            return mFields[index];
        }

        public override string OriginalSchemaName()
        {
            if (SchemaNode != null && SchemaNode.Name != null && SchemaNode.Name != String.Empty )
            {
                return SchemaNode.Name;
            }
            if (Name.Contains("TYPE_"))
            {
                return Name.Substring(5, Name.Length - 5);
            }

            return Name;
        }

        public bool Initialized
        {
            get { return mInitialized; }
            set { mInitialized = value; }
        }
        
        public virtual string DropSQL()
        {
            string dropExpress = string.Empty;           

            List<OracleSchemaTable> dependencyTableList = new List<OracleSchemaTable>();

            if (Fields.Count > 0)
            {
                for (int index = 0; index < Fields.Count; index++)
                {
                    if (Fields[index] is OracleSchemaCollectionField)
                    {
                        dependencyTableList.Add((Fields[index] as OracleSchemaCollectionField).CollectionTable);
                    }
                }
            }

            if (dependencyTableList.Count > 0)
            {
                foreach (OracleSchemaTable item in dependencyTableList)
                {
                    dropExpress += item.DropSQL();
                }
            }

            return string.Format("{0}DROP TABLE {1};{2}", dropExpress, DatabaseName.ToUpper(), Environment.NewLine);
        }
    }

	public class OracleSchemaReferenceField : OracleSchemaField
    {
        private OracleSchemaTable mReferencedTable;

        public OracleSchemaReferenceField(string name, XmlSchemaType schemaNode, bool nullable, OracleSchemaTable referencedTable)
            : base(name, schemaNode, nullable)
        {
            mReferencedTable = referencedTable;
        }

        public override string SQLExpress()
        {
            return string.Format("  {0} {1} {2}", DatabaseName, OracleSchemaVisitor.IDTypeExpress(), Nullable ? "NULL" : "NOT NULL");
        }

        public override string DatabaseName
        {
            get
            {
                string typeName = (FilteredDBName.Length > 23 ? FilteredDBName.Substring(0, 23) : FilteredDBName);
                return string.Format("REF_ID_{0}", typeName);
            }
        }

        public OracleSchemaTable ReferencedTable
        {
            get { return mReferencedTable; }
        }
    }

	public class OracleSchemaChoiceField : OracleSchemaField
    {
        private Dictionary<string, string> mChoiceTableList;
        private int mPropertyIndex;
        private XmlSchemaChoice mChoiceNode;

        public OracleSchemaChoiceField(string name, XmlSchemaType schemaNode, bool nullable, int index, XmlSchemaChoice choiceNode)
            : base(name, schemaNode, nullable)
        {
            mPropertyIndex = index;
            mChoiceNode = choiceNode;
            mChoiceTableList = new Dictionary<string, string>();
            foreach (XmlSchemaObject item in mChoiceNode.Items)
            {
                if (item is XmlSchemaElement)
                {
                    XmlSchemaElement element = item as XmlSchemaElement;
                    string type = element.ElementSchemaType.Name;
                    mChoiceTableList.Add(element.Name, type);
                }
            }
        }

        public override string DatabaseName
        {
            get
            {
                return string.Format("[CHOICE {0}]", mPropertyIndex);
            }
        }

        public override string SQLExpress()
        {
            string isNullStr = (Nullable ? "NULL" : "NOT NULL");
            return string.Format(" {0} {1} {2},{3}{4} {5} {6}", TypeFieldName, "VARCHAR2(30)", isNullStr, Environment.NewLine
                , ItemFieldName, OracleSchemaVisitor.IDTypeExpress(), isNullStr);
        }

        public override string OriginalSchemaName()
        {
            return string.Format("[CHOICE {0}]", mPropertyIndex);
        }

        public string TypeFieldName
        {
            get { return string.Format("CHOICE_TYPE_{0}", mPropertyIndex); }
        }

        public string ItemFieldName
        {
            get { return string.Format("CHOICE_ITEM_ID_{0}", mPropertyIndex); }
        }

        public Dictionary<string, string> ChoicedTypeList
        {
            get { return mChoiceTableList; }
        }

		 public int PropertyIndex
		 {
			 get { return mPropertyIndex; }
		 }
    }

	public class OracleSchemaDomainField : OracleSchemaReferenceField
    {
        public OracleSchemaDomainField(string name, XmlSchemaType schemaNode, bool nullable, OracleSchemaTable referencedTable)
            : base(name, schemaNode, nullable, referencedTable)
        { }

        public override string DatabaseName
        {
            get
            {
                string typeName = (FilteredDBName.Length > 23 ? FilteredDBName.Substring(0, 23) : FilteredDBName);
                return string.Format("DOMAIN_{0}", typeName);
            }
        }
    }

	public class OracleSchemaDependencyField : OracleSchemaField
    {
        private OracleSchemaTable mDependencyTable;

        public OracleSchemaDependencyField(string name, XmlSchemaType schemaNode, OracleSchemaTable dependencyTable)
            : base(name, schemaNode, false)
        {
            mDependencyTable = dependencyTable;
        }

        public OracleSchemaTable DependencyTable
        {
            get { return mDependencyTable; }
        }

        public override string DatabaseName
        {
            get
            {
                // TODO: how to resolve the duplicate table name for REL_Deckel_DeckelType case?
                //string typeName = (DependencyTable.DatabaseName.Length > 26 ? DependencyTable.DatabaseName.Substring(0, 26) : DependencyTable.DatabaseName);
                //return string.Format("PID_{0}", typeName);
                return "PID_FK";
            }
        }
    }

	public class OracleSchemaGeomField : OracleSchemaField
    {
        public readonly static string GEOMTypeExpress = "SDO_GEOMETRY";

        public OracleSchemaGeomField(string name, XmlSchemaType schemaNode, bool nullable)
            : base(name, schemaNode, nullable)
        { }

        public override string DatabaseName
        {
            get
            {
                string typeName = (Name.Length > 25 ? Name.Substring(0, 25) : Name);
                return string.Format("GEOM_{0}", typeName);
            }
        }

        public override string SQLExpress()
        {
            return string.Format("  {0} {1} {2}", DatabaseName, GEOMTypeExpress, Nullable ? "NULL" : "NOT NULL");
        }
    }

	public class OracleSchemaCollectionField : OracleSchemaReferenceField
    {
        private OracleSchemaTable mCollectionTable;

        public OracleSchemaCollectionField(string name, XmlSchemaType schemaNode, bool nullable, OracleSchemaTable referencedTable, OracleSchemaTable parentTable)
            : base(name, schemaNode, nullable, referencedTable)
        {
            string tableName = string.Format("REL_{0}_{1}", Name, ReferencedTable.OriginalSchemaName());
            mCollectionTable = new OracleSchemaTable(tableName, null);
            OracleSchemaField pkField = OracleSchemaVisitor.CreateRefIDField();
            OracleSchemaDependencyField pidField = new OracleSchemaDependencyField(name, schemaNode, parentTable);
            pidField.TypeExpress = OracleSchemaVisitor.IDTypeExpress();
            OracleSchemaReferenceField childField = new OracleSchemaReferenceField(name, schemaNode, false, referencedTable);

            mCollectionTable.AddField(pkField);
            mCollectionTable.AddField(pidField);
            mCollectionTable.AddField(childField);
        }

        public OracleSchemaTable CollectionTable
        {
            get { return mCollectionTable; }
        }

        public override string DatabaseName
        {
            get
            {
                string typeName = (FilteredDBName.Length > 26 ? FilteredDBName.Substring(0, 26) : FilteredDBName);
                return string.Format("HAS_{0}", typeName);
            }
        }

        public override string SQLExpress()
        {
            return string.Format("  {0} {1} {2}", DatabaseName, OracleSchemaVisitor.BoolTypeExpress(), Nullable ? "NULL" : "NOT NULL");
        }
    }

	public class OracleSchemaGeomTable : OracleSchemaTable
    {
        public readonly static string TABLE_NAME = "GEOM_POINT";
        public readonly static string FIELD_DATA = "DATA";

        public OracleSchemaGeomTable(string name, XmlSchemaType schemaNode)
            : base(TABLE_NAME, schemaNode)
        {
            OracleSchemaField field_ID = new OracleSchemaField(OracleSchemaVisitor.FIELD_AUTO_ID, null, false);
            field_ID.TypeExpress = OracleSchemaVisitor.IDTypeExpress();
            OracleSchemaGeomField field_Data = new OracleSchemaGeomField(FIELD_DATA, null, false);

            AddField(field_ID);
            AddField(field_Data);
        }

        public override string OriginalSchemaName()
        {
            return Name;
        }

        public override string DatabaseName
        {
            get
            {
                return Name;
            }
        }

    }

	public class OracleSchemaDomainTable : OracleSchemaTable
    {
        public readonly static string FIELD_KEY = "ID";
        public readonly static string FIELD_VALUE = "VALUE";
        public readonly static string FIELD_SHORTVALUE = "SHORT_VALUE";

        private List<OracleSchemaDomainValue> mEnumValues;

        public OracleSchemaDomainTable(string name, XmlSchemaType schemaNode)
            : base(name, schemaNode)
        {
            OracleSchemaField field_ID = new OracleSchemaField(FIELD_KEY, null, false);
            field_ID.TypeExpress = OracleSchemaVisitor.IDTypeExpress();
            OracleSchemaField field_Value = new OracleSchemaField(FIELD_VALUE, null, true);
            field_Value.TypeExpress = "VARCHAR2(255)";
            OracleSchemaField field_shortValue = new OracleSchemaField(FIELD_SHORTVALUE, null, true);
            field_shortValue.TypeExpress = "VARCHAR2(20)";

            AddField(field_ID);
            AddField(field_shortValue);
            AddField(field_Value);

            mEnumValues = new List<OracleSchemaDomainValue>();
        }

        public List<OracleSchemaDomainValue> EnumValues
        {
            get { return mEnumValues; }
        }

        public void AddValue(OracleSchemaDomainValue item)
        {
            mEnumValues.Add(item);
        }

        public override string SQLExpress()
        {
            string createSQL = base.SQLExpress();
            foreach (OracleSchemaDomainValue item in mEnumValues)
            {
                createSQL += String.Format("INSERT INTO {0} ({1}, {2}, {3}) VALUES ({4}, '{5}', '{6}');", DatabaseName, FIELD_KEY, FIELD_SHORTVALUE, FIELD_VALUE, item.Key, item.ShortValue, item.Value) + Environment.NewLine;
            }
            return createSQL;
        }
        public string UpdateValues()
        {
            string updateState = string.Empty;
            foreach (OracleSchemaDomainValue item in mEnumValues)
            {
                string itemValue = item.Value;
                if (itemValue.Contains("'"))
                {
                    itemValue = itemValue.Replace("'", "\\'");
                }
                updateState += string.Format("UPDATE {0} SET {1}='{2}' WHERE {3}='{4}';{5}", DatabaseName, FIELD_VALUE, itemValue, FIELD_SHORTVALUE, item.ShortValue, Environment.NewLine);
            }
            return updateState;
        }
    }
}
